package other;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import db.DBConnectionFactory;
import db.DBConnector;


public class CodeDescriptionPair {

	public final static String TABLE_TYPE = "assetTypes";
	public final static String TABLE_MINTENANCE = "maintenanceSchedules";
	public final static String TABLE_FINANCIAL = "financialValues";
	public final static String TABLE_CONFIDENTIALITY = "confidentialityValues";
	public final static String TABLE_INTEGRITY = "integrityValues";
	public final static String TABLE_AVAILABILITY = "availabilityValues";
	public final static String TABLE_CLASSIFICATION = "assetClassifications";
	public final static String TABLE_USER_TYPES= "UserTypes";
	
	public static String getCode(String tableName, String description){
		ArrayList<CodeDescriptionPair> cdps = getCodeDescriptionPairsFor(tableName);
		
		for(int i=0;i<cdps.size();i++)
			if(cdps.get(i).getDescription().equals(description))
				return cdps.get(i).getCode();
				
		return null;
	}
	
	public static String getDescription(String tableName, String code){
		ArrayList<CodeDescriptionPair> cdps = getCodeDescriptionPairsFor(tableName);
		
		for(int i=0;i<cdps.size();i++)
			if(cdps.get(i).getCode().equals(code))
				return cdps.get(i).getDescription();
				
		return null;
	}

	
	public static ArrayList<CodeDescriptionPair> getCodeDescriptionPairsFor(String tableName)
	{
		ArrayList<CodeDescriptionPair> codeDescriptionPairs = new ArrayList<CodeDescriptionPair>();
		
		String query = "SELECT * FROM "+tableName+";";
		//Connection con = DBConnectionFactory.getInstance().getConnection();
		Connection con = DBConnectionFactory.getInstance().getConnection();
		ResultSet rs = DBConnector.executeQuery(con, query);

		try {
			while(rs.next())
				codeDescriptionPairs.add(new CodeDescriptionPair(rs.getString("code"), tableName));
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return codeDescriptionPairs;
	}	
	
	private String code;
	private String description;
	private String sourceTableName;
	
	public CodeDescriptionPair(String code, String sourceTableName)
	{
		this.sourceTableName = sourceTableName;
		setCode(code);
	}
	
	public String getCode()
	{
		return code;
	}
	
	public void setCode(String code)
	{		
		//Check if code is valid
		String query = "SELECT count(*) FROM "+sourceTableName+" WHERE code = \""+code+"\";";
		Connection con = DBConnectionFactory.getInstance().getConnection();
		int count;
		try {
			count = Integer.parseInt(DBConnector.executeQueryWithSingleValue(con, query));
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			count = 0; //make the code invalid if there was an exception by setting count to 0
		}
		
		if(count > 0)
		{
			this.code = code;	
			setDescription();
		}
	}
	
	public String getDescription()
	{
		return description;
	}
	
	private void setDescription(){
		//Check if code is valid
		String query = "SELECT description FROM "+sourceTableName+" WHERE code = \""+code+"\";";
		Connection con = DBConnectionFactory.getInstance().getConnection();
	
		try {
			this.description = DBConnector.executeQueryWithSingleValue(con, query);
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}
	
}
